package zju.ccnt.rest.service.util.sql;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Locale;
import java.util.Map;

/**
 * Created by zha on 14/11/20.
 */
public class JdbcViewUtil implements ViewUtil {
    private final JdbcTemplate jdbcTemplate;
    private final String CREATE_VIEW = "create view ";
    private final String QUERY_VIEW = "select * from ";
    private final String QUERY_COUNT = "SELECT count(id) as total FROM ";

    public JdbcViewUtil(DataSource dataSource){
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    private String createViewSql(SqlPersistence sqlQuery, String viewName){
        return CREATE_VIEW + viewName + " AS " + sqlQuery.getSql();
    }

    private String queryViewSql(String viewName, int start, int size, String limit){
        String constriant = limit == null ? "" : " where " + limit;
        return QUERY_VIEW + viewName + constriant + " LIMIT " + size + " OFFSET " + start;
    }

    private String queryViewCount(String viewName, String limit){
        String constriant = limit == null ? "" : " where " + limit;
        return QUERY_COUNT + viewName + constriant;
    }

    @Override
    public void execute(SqlPersistence sqlQuery, String viewName) {
        this.jdbcTemplate.execute(createViewSql(sqlQuery, viewName));
    }

    @Override
    public List<Map<String, Object>> QueryAll(String viewName, int start, int count, String limit) {
        List<Map<String, Object>> res = jdbcTemplate.queryForList(queryViewSql(viewName, start, count, limit));
        return res;
    }

    @Override
    public long count(String viewName, String limit) {
        long total = (Long)jdbcTemplate.queryForMap(queryViewCount(viewName, limit)).get("total");
        return total;
    }
}
